package easybooking.db;

import easybooking.data.Customer;
import easybooking.data.CustomerImpl;
import java.rmi.RemoteException;
import java.util.Vector;
import java.sql.*;

/**
 * Mapper-Class, which displays <code>Customer</code> objects in a relational
 * database. Therefore a collection of methods to search, create and modify
 * will be provided.
 * The mapping is bidirectional, which means, that objects could be transformed
 * into database-structures and database-structures into objects.
 *
 * @author Mark Roman Stehle
 */
// <editor-fold defaultstate="collapsed" desc=" UML Marker "> 
// #[regen=yes,id=DCE.207DE349-477A-85F8-F47D-53D189D8C01D]
// </editor-fold> 
public class CustomerMapper extends Mapper {

    /**
     * The class <code>CustomerMapper</code> will be instantiated only once.
     * This is called <b>Singleton</b>, because of the <code>static</code>-tag,
     * the variable exists only once for all instances of this class. The
     * variable saves the only instance of this class.
     *
     * @see customerMapper()
     */
    // <editor-fold defaultstate="collapsed" desc=" UML Marker "> 
    // #[regen=yes,id=DCE.76D6F1D3-251F-1E91-3583-B426B62D867B]
    // </editor-fold> 
    private static CustomerMapper customerMapper = null;

    /**
     * The protected constructor prevents the possibility to generate a new
     * instance of this class.
     */
    protected CustomerMapper() {
    }

    /**
     * The static method can be called with
     * <code>CustomerMapper.customerMapper()</code>.
     * This guarantees the singleton property by checking so that only one
     * instance of the <code>CustomerMapper</code> exists.

     * <b>Conclusion:</b> The <code>CustomerMapper</code> should not be
     * instantiated with <code>new</code>, but rather by calling the static
     * method.

     * @return The <code>CustomerMapper</code> object.
     * @see customerMapper
     */
    // <editor-fold defaultstate="collapsed" desc=" UML Marker "> 
    // #[regen=yes,id=DCE.7C6AF305-EA6C-AFDB-D058-6C3E5436DEEA]
    // </editor-fold> 
    public static CustomerMapper customerMapper() {
        if (customerMapper == null) {
            customerMapper = new CustomerMapper();
        }
        return customerMapper;
    }

    /**
     * Searching a customer with given key, since the key is distinct only one
     * object will be returned.
     *
     * @param id primary key (->DB)
     * @return Customer object, which corresponds the given key,
     * <code>null</code> for a non existing database tupel.
     */
    // <editor-fold defaultstate="collapsed" desc=" UML Marker "> 
    // #[regen=yes,id=DCE.4A077371-6959-0046-3AA4-2D98C5A75C94]
    // </editor-fold> 
    public Customer findByKey(int id) {
        Connection con = DBConnection.connection();
        try {
            Statement stmt = con.createStatement();
            ResultSet rs = stmt.executeQuery(
                    "SELECT customerId, firstname, lastname, street, zip, city, isActive FROM Customer " +
                    "WHERE customerId=" + id);
            if (rs.next()) {
                try {
                    Customer c = new CustomerImpl();
                    c.setCustomerId(rs.getInt("customerId"));
                    c.setFirstname(rs.getString("firstname"));
                    c.setLastname(rs.getString("lastname"));
                    c.setStreet(rs.getString("street"));
                    c.setZip(rs.getInt("zip"));
                    c.setCity(rs.getString("city"));
                    c.setIsActive(rs.getBoolean("isActive"));
                    return c;
                } catch (Exception ex) {
                    ex.printStackTrace();
                    return null;
                }
            }
        } catch (SQLException e2) {
            e2.printStackTrace();
            return null;
        }
        return null;
    }

    /**
     * Selecting all active customers from the database.
     *
     * @return A vector of customer objects, which includes all active customers.
     * In case of an exception, an empty vector will be returned.
     */
    // <editor-fold defaultstate="collapsed" desc=" UML Marker "> 
    // #[regen=yes,id=DCE.86B387B3-D083-43AE-FF2D-931890555A1C]
    // </editor-fold> 
    public Vector<Customer> findAll() {
        Connection con = DBConnection.connection();
        Vector<Customer> result = new Vector<Customer>();
        try {
            Statement stmt = con.createStatement();
            ResultSet rs = stmt.executeQuery(
                    "SELECT customerId, firstname, lastname, street, zip, city, isActive FROM Customer " +
                    "WHERE isActive = true " +
                    "ORDER BY customerId ASC");
            while (rs.next()) {
                try {
                    Customer c = new CustomerImpl();
                    c.setCustomerId(rs.getInt("customerId"));
                    c.setFirstname(rs.getString("firstname"));
                    c.setLastname(rs.getString("lastname"));
                    c.setStreet(rs.getString("street"));
                    c.setZip(rs.getInt("zip"));
                    c.setCity(rs.getString("city"));
                    c.setIsActive(rs.getBoolean("isActive"));
                    result.addElement(c);
                } catch (Exception ex) {
                    ex.printStackTrace();
                    break;
                }
            }
        } catch (SQLException e2) {
            e2.printStackTrace();
        }
        return result;
    }

    /**
     * Inserting a <code>Customer</code> object into the database. Before that,
     * the primary key of the given object will be verified und corrected.
     *
     * @param c the object to be saved
     * @return The given object, with corrected primary key.
     */
    // <editor-fold defaultstate="collapsed" desc=" UML Marker "> 
    // #[regen=yes,id=DCE.491E271E-E7DB-9DCB-609F-E6EDBD6A4EBD]
    // </editor-fold> 
    public Customer insert(Customer c) {
        Connection con = DBConnection.connection();
        try {
            Statement stmt = con.createStatement();
            ResultSet rs = stmt.executeQuery(
                    "SELECT MAX(customerId) AS maxCustomerId " +
                    "FROM Customer");
            if (rs.next()) {
                try {
                    c.setCustomerId(rs.getInt("maxCustomerId") + 1);
                    stmt = con.createStatement();
                    stmt.executeUpdate("INSERT INTO Customer (customerId, firstname, lastname, street, zip, city, isActive) " +
                            "VALUES (" +
                            c.getCustomerId() + ",'" +
                            c.getFirstname() + "','" +
                            c.getLastname() + "','" +
                            c.getStreet() + "','" +
                            c.getZip() + "','" +
                            c.getCity() + "','" +
                            booleanToInt(c.getIsActive()) + "')");
                } catch (RemoteException ex) {
                    ex.printStackTrace();
                }
            }
        } catch (SQLException e2) {
            e2.printStackTrace();
        }
        return c;
    }

    /**
     * Rewriting an object into the database.
     *
     * @param c the object which sould be written into the database
     * @return The given object.
     */
    // <editor-fold defaultstate="collapsed" desc=" UML Marker "> 
    // #[regen=yes,id=DCE.A4C82356-05F3-93C7-6B4B-06A488E7B9D0]
    // </editor-fold> 
    public Customer update(Customer c) {
        Connection con = DBConnection.connection();
        try {
            Statement stmt = con.createStatement();
            stmt.executeUpdate("UPDATE Customer " +
                    "SET firstname=\"" + c.getFirstname() + "\", " +
                    "lastname=\"" + c.getLastname() + "\", " +
                    "street=\"" + c.getStreet() + "\", " +
                    "zip=\"" + c.getZip() + "\", " +
                    "city=\"" + c.getCity() + "\", " +
                    "isActive=\"" + booleanToInt(c.getIsActive()) + "\" " +
                    "WHERE customerId=" + c.getCustomerId());
        } catch (SQLException e2) {
            e2.printStackTrace();
        } catch (RemoteException e) {
            e.printStackTrace();
        }
        return c;
    }

    /**
     * Selecting all active customers from the database with a certain lastname,
     * which is given through a parameter.
     *
     * @param lastname the lastname or part of the lastname which should be
     * searched for
     * @return A vector of cutomer objects, which includes all active customers
     * with a certain lastname.
     * In case of an exception, an empty vector will be returned.
     */
    // <editor-fold defaultstate="collapsed" desc=" UML Marker "> 
    // #[regen=yes,id=DCE.0E367E00-AE87-D3E2-AD18-FBCF45B7EAAB]
    // </editor-fold> 
    public Vector<Customer> findByLastname(String lastname) {
        Connection con = DBConnection.connection();
        Vector<Customer> result = new Vector<Customer>();
        try {
            Statement stmt = con.createStatement();
            ResultSet rs = stmt.executeQuery(
                    "SELECT customerId, firstname, lastname, street, zip, city, isActive FROM Customer " +
                    "WHERE lastname LIKE '%" + lastname + "%'" +
                    "AND isActive = true " +
                    "ORDER BY customerId ASC");
            while (rs.next()) {
                try {
                    Customer c = new CustomerImpl();
                    c.setCustomerId(rs.getInt("customerId"));
                    c.setFirstname(rs.getString("firstname"));
                    c.setLastname(rs.getString("lastname"));
                    c.setStreet(rs.getString("street"));
                    c.setZip(rs.getInt("zip"));
                    c.setCity(rs.getString("city"));
                    c.setIsActive(rs.getBoolean("isActive"));
                    result.addElement(c);
                } catch (Exception ex) {
                    ex.printStackTrace();
                    break;
                }
            }
        } catch (SQLException e2) {
            e2.printStackTrace();
        }
        return result;
    }
}

